import { Op } from 'sequelize';
import { QueryTypes } from 'sequelize';
import path from 'path';
import fs from 'fs';
import { promisify } from 'util';
import JSZip from 'jszip';
import sequelize from '@/config/database';
import { GenTable, GenTableColumn } from '@/models/tool';
import GenUtils from '@/utils/generator/generator.util';
import TemplateEngine from '@/utils/generator/template-engine';
import genConfig from '@/config/generator';
import { DatabaseError } from "@/utils/errors";
import { getLogger } from '@/utils/logger';

const logger = getLogger('gen-service');
const writeFileAsync = promisify(fs.writeFile);
const mkdirAsync = promisify(fs.mkdir);
const existsAsync = promisify(fs.exists);

/**
 * 代码生成服务类
 */
class GenService {
  /**
   * 查询业务表列表
   * @param query 查询条件
   * @returns 业务表列表
   */
  public async selectGenTableList(query: any): Promise<GenTable[]> {
    const { tableName, tableComment, createBy } = query;
    const where: any = {};

    // 构建查询条件
    if (tableName) {
      where.tableName = { [Op.like]: `%${tableName}%` };
    }
    if (tableComment) {
      where.tableComment = { [Op.like]: `%${tableComment}%` };
    }
    if (createBy) {
      where.createBy = createBy;
    }

    // 查询业务表
    const genTables = await GenTable.findAll({
      where,
      order: [['createTime', 'DESC']]
    });

    return genTables;
  }

  /**
   * 查询业务表分页列表
   * @param query 查询条件
   * @returns 业务表分页列表
   */
  public async selectGenTableListPaged(query: any): Promise<{ rows: GenTable[], count: number }> {
    const { tableName, tableComment, createBy, pageNum = 1, pageSize = 10 } = query;
    const offset = (pageNum - 1) * pageSize;
    const limit = pageSize;
    const where: any = {};

    // 构建查询条件
    if (tableName) {
      where.tableName = { [Op.like]: `%${tableName}%` };
    }
    if (tableComment) {
      where.tableComment = { [Op.like]: `%${tableComment}%` };
    }
    if (createBy) {
      where.createBy = createBy;
    }

    // 查询业务表
    const { rows, count } = await GenTable.findAndCountAll({
      where,
      order: [['createTime', 'DESC']],
      offset,
      limit
    });

    return { rows, count };
  }

  /**
   * 查询数据库表列表
   * @param query 查询条件
   * @returns 数据库表列表
   */
  public async selectDbTableList(query: any): Promise<any[]> {
    const { tableName, tableComment, pageNum = 1, pageSize = 10 } = query;
    
    // 用于存储查询条件
    const whereClause: string[] = ['table_schema = database()'];

    // 排除已经导入的表
    const genTableNames = await this.getGenTableNames();
    if (genTableNames.length > 0) {
      whereClause.push(`table_name NOT IN ('${genTableNames.join("','")}')`);
    }

    // 添加其他查询条件
    if (tableName) {
      whereClause.push(`table_name LIKE '%${tableName}%'`);
    }
    if (tableComment) {
      whereClause.push(`table_comment LIKE '%${tableComment}%'`);
    }

    // 构建SQL查询
    const sql = `SELECT 
                  table_name, table_comment, create_time, update_time 
                FROM 
                  information_schema.tables 
                WHERE 
                  ${whereClause.join(' AND ')} 
                ORDER BY 
                  create_time DESC
                LIMIT ${(pageNum - 1) * pageSize}, ${pageSize}`;

    // 统计查询
    const countSql = `SELECT 
                        COUNT(*) as count 
                      FROM 
                        information_schema.tables 
                      WHERE 
                        ${whereClause.join(' AND ')}`;

    // 执行查询
    const [rows, countResult] = await Promise.all([
      sequelize.query(sql, { type: QueryTypes.SELECT }),
      sequelize.query(countSql, { type: QueryTypes.SELECT })
    ]);

    // 返回结果
    return rows.map((row: any) => ({
      tableName: row.table_name,
      tableComment: row.table_comment,
      createTime: row.create_time,
      updateTime: row.update_time
    }));
  }

  /**
   * 查询数据库表列表（分页）
   * @param query 查询条件
   * @returns 数据库表列表
   */
  public async selectDbTableListPaged(query: any): Promise<{ rows: any[], count: number }> {
    const { tableName, tableComment, pageNum = 1, pageSize = 10 } = query;
    
    // 用于存储查询条件
    const whereClause: string[] = ['table_schema = database()'];

    // 排除已经导入的表
    const genTableNames = await this.getGenTableNames();
    if (genTableNames.length > 0) {
      whereClause.push(`table_name NOT IN ('${genTableNames.join("','")}')`);
    }

    // 添加其他查询条件
    if (tableName) {
      whereClause.push(`table_name LIKE '%${tableName}%'`);
    }
    if (tableComment) {
      whereClause.push(`table_comment LIKE '%${tableComment}%'`);
    }

    // 构建SQL查询
    const sql = `SELECT 
                  table_name, table_comment, create_time, update_time 
                FROM 
                  information_schema.tables 
                WHERE 
                  ${whereClause.join(' AND ')} 
                ORDER BY 
                  create_time DESC
                LIMIT ${(pageNum - 1) * pageSize}, ${pageSize}`;

    // 统计查询
    const countSql = `SELECT 
                        COUNT(*) as count 
                      FROM 
                        information_schema.tables 
                      WHERE 
                        ${whereClause.join(' AND ')}`;

    // 执行查询
    const [rows, countResult] = await Promise.all([
      sequelize.query(sql, { type: QueryTypes.SELECT }),
      sequelize.query(countSql, { type: QueryTypes.SELECT })
    ]);

    // 返回结果
    return {
      rows: rows.map((row: any) => ({
        tableName: row.table_name,
        tableComment: row.table_comment,
        createTime: row.create_time,
        updateTime: row.update_time
      })),
      count: (countResult as any)[0].count
    };
  }

  /**
   * 根据表名查询数据库表列表
   * @param tableNames 表名数组
   * @returns 数据库表列表
   */
  public async selectDbTableListByNames(tableNames: string[]): Promise<any[]> {
    if (!tableNames || tableNames.length === 0) {
      return [];
    }
    
    const tableNameStr = tableNames.map(name => `'${name}'`).join(',');
    const sql = `SELECT 
                  table_name, table_comment, create_time, update_time 
                FROM 
                  information_schema.tables 
                WHERE 
                  table_schema = database() 
                  AND table_name IN (${tableNameStr})
                ORDER BY 
                  create_time DESC`;
    
    // 执行查询
    const rows = await sequelize.query(sql, { type: QueryTypes.SELECT });
    
    // 返回结果
    return rows.map((row: any) => ({
      tableName: row.table_name,
      tableComment: row.table_comment,
      createTime: row.create_time,
      updateTime: row.update_time
    }));
  }

  /**
   * 查询所有已生成表信息
   * @returns 表信息列表
   */
  public async selectGenTableAll(): Promise<GenTable[]> {
    return await GenTable.findAll({
      order: [['createTime', 'DESC']]
    });
  }

  /**
   * 查询业务表信息
   * @param id 业务表ID
   * @returns 业务表信息
   */
  public async selectGenTableById(id: number): Promise<GenTable | null> {
    const genTable = await GenTable.findByPk(id, {
      include: [{
        model: GenTableColumn,
        as: 'columns',
        order: [['sort', 'ASC']]
      }]
    });
    
    // 如果是主子表，需要加载子表信息
    if (genTable && genTable.tplCategory === 'sub' && genTable.subTableName) {
      const subTable = await GenTable.findOne({
        where: { tableName: genTable.subTableName },
        include: [{
          model: GenTableColumn,
          as: 'columns',
          order: [['sort', 'ASC']]
        }]
      });
      
      if (subTable) {
        genTable.subTable = subTable;
      }
    }

    // 设置主键列
    if (genTable) {
      const pkColumn = genTable.columns?.find(col => col.isPk === '1');
      if (pkColumn) {
        genTable.pkColumn = pkColumn;
      }
    }
    
    return genTable;
  }

  /**
   * 查询表字段列表
   * @param tableId 表ID
   * @returns 字段列表
   */
  public async selectGenTableColumnListByTableId(tableId: number): Promise<GenTableColumn[]> {
    return await GenTableColumn.findAll({
      where: { tableId },
      order: [['sort', 'ASC']]
    });
  }

  /**
   * 导入表结构
   * @param tableList 表列表
   * @param operName 操作人
   */
  public async importGenTable(tableList: any[], operName: string): Promise<void> {
    if (!tableList || tableList.length === 0) {
      return;
    }

    for (const table of tableList) {
      // 查询表结构
      const tableName = table.tableName;
      const tableComment = table.tableComment;

      // 创建GenTable对象
      const genTable = new GenTable();
      genTable.tableName = tableName;
      genTable.tableComment = tableComment;

      // 初始化表信息
      GenUtils.initTable(genTable, operName);

      // 获取列信息
      const dbColumns = await this.getDbTableColumns(tableName);
      
      // 保存数据到gen_table表
      try {
        await sequelize.transaction(async (transaction) => {
          // 保存业务表信息
          await genTable.save({ transaction });
          
          // 保存表字段信息
          const columnList: GenTableColumn[] = [];
          for (const column of dbColumns) {
            const genTableColumn = new GenTableColumn();
            
            // 设置字段基本信息
            genTableColumn.tableId = genTable.tableId;
            genTableColumn.columnName = column.column_name;
            genTableColumn.columnComment = column.column_comment;
            genTableColumn.columnType = column.column_type;
            genTableColumn.sort = column.ordinal_position;
            
            // 初始化字段信息
            GenUtils.initColumnField(genTableColumn, genTable);
            
            // 添加到批量操作列表
            columnList.push(genTableColumn);
          }
          
          // 批量保存列信息
          if (columnList.length > 0) {
            await GenTableColumn.bulkCreate(
              columnList.map(col => col.toJSON()),
              { transaction }
            );
          }
        });
      } catch (error: any) {
        logger.error(`导入表 ${tableName} 失败:`, error);
        throw new DatabaseError(`导入表 ${tableName} 失败: ${error.message}`);
      }
    }
  }

  /**
   * 创建表
   * @param sql 建表SQL
   * @returns 是否成功
   */
  public async createTable(sql: string): Promise<boolean> {
    if (!sql) {
      return false;
    }

    try {
      // 执行建表SQL
      await sequelize.query(sql, {
        type: QueryTypes.RAW
      });
      return true;
    } catch (error: any) {
      logger.error('创建表失败:', error);
      throw new DatabaseError(`创建表失败: ${error.message}`);
    }
  }

  /**
   * 修改业务表
   * @param genTable 业务表信息
   */
  public async updateGenTable(genTable: GenTable): Promise<void> {
    try {
      // 验证更新
      await this.validateEdit(genTable);

      const { columns = [], ...tableData } = genTable;
      
      await sequelize.transaction(async (transaction) => {
        // 更新业务表信息
        await GenTable.update(tableData, {
          where: { tableId: genTable.tableId },
          transaction
        });

        // 删除已有的字段
        if (genTable.tableId) {
          await GenTableColumn.destroy({
            where: { tableId: genTable.tableId },
            transaction
          });
        }

        // 重新插入字段
        if (columns.length > 0) {
          await GenTableColumn.bulkCreate(columns, { transaction });
        }
      });
    } catch (error: any) {
      logger.error('修改业务表失败:', error);
      throw new DatabaseError(`修改业务表失败: ${error.message}`);
    }
  }

  /**
   * 删除业务表
   * @param tableIds 表ID数组
   */
  public async deleteGenTableByIds(tableIds: number[]): Promise<void> {
    if (!tableIds || tableIds.length === 0) {
      return;
    }

    try {
      await sequelize.transaction(async (transaction) => {
        // 删除字段
        await GenTableColumn.destroy({
          where: { tableId: { [Op.in]: tableIds } },
          transaction
        });

        // 删除表
        await GenTable.destroy({
          where: { tableId: { [Op.in]: tableIds } },
          transaction
        });
      });
    } catch (error: any) {
      logger.error('删除业务表失败:', error);
      throw new DatabaseError(`删除业务表失败: ${error.message}`);
    }
  }

  /**
   * 同步数据库
   * @param tableName 表名
   */
  public async synchDb(tableName: string): Promise<void> {
    try {
      // 查询表信息
      const genTable = await GenTable.findOne({
        where: { tableName },
        include: [{
          model: GenTableColumn,
          as: 'columns'
        }]
      });

      if (!genTable) {
        throw new Error(`表 ${tableName} 不存在`);
      }

      // 查询数据库中的列
      const dbColumns = await this.getDbTableColumns(tableName);
      
      // 将新列添加到已有列中
      const genTableColumns = genTable.columns || [];
      const dbColumnNames = dbColumns.map((col: any) => col.column_name);
      const tableColumnNames = genTableColumns.map(col => col.columnName);
      
      await sequelize.transaction(async (transaction) => {
        // 删除业务表中不存在的字段
        for (const column of genTableColumns) {
          if (!dbColumnNames.includes(column.columnName)) {
            await GenTableColumn.destroy({
              where: { columnId: column.columnId },
              transaction
            });
          }
        }

        // 新增表中新增加的字段
        for (const dbColumn of dbColumns) {
          if (!tableColumnNames.includes(dbColumn.column_name)) {
            // 创建新的列
            const genTableColumn = new GenTableColumn();
            
            genTableColumn.tableId = genTable.tableId;
            genTableColumn.columnName = dbColumn.column_name;
            genTableColumn.columnComment = dbColumn.column_comment;
            genTableColumn.columnType = dbColumn.column_type;
            genTableColumn.sort = dbColumn.ordinal_position;
            
            // 初始化字段信息
            GenUtils.initColumnField(genTableColumn, genTable);
            
            // 保存
            await genTableColumn.save({ transaction });
          }
        }
      });
    } catch (error: any) {
      logger.error('同步数据库失败:', error);
      throw new DatabaseError(`同步数据库失败: ${error.message}`);
    }
  }

  /**
   * 预览代码
   * @param tableId 表ID
   * @returns 预览代码
   */
  public async previewCode(tableId: number): Promise<Map<string, string>> {
    const genTable = await this.selectGenTableById(tableId);
    if (!genTable) {
      throw new Error(`表 ID ${tableId} 不存在`);
    }

    // 准备模板变量
    const context = await TemplateEngine.prepareContext(genTable);

    // 获取模板列表
    const templates = TemplateEngine.getTemplateList(
      genTable.tplCategory,
      genTable.tplWebType || 'vue'
    );

    // 渲染模板
    const codeMap = new Map<string, string>();
    for (const template of templates) {
      try {
        // 渲染模板
        const code = await TemplateEngine.renderTemplate(template, context);
        
        // 获取生成文件名
        const fileName = TemplateEngine.getFileName(template, genTable);
        
        // 添加到结果集
        codeMap.set(fileName, code);
      } catch (err) {
        console.error(`渲染模板 ${template} 失败:`, err);
      }
    }

    return codeMap;
  }

  /**
   * 生成代码（自定义路径）
   * @param tableName 表名
   * @returns 是否成功
   */
  public async generatorCode(tableName: string): Promise<boolean> {
    // 检查是否允许覆盖本地文件
    if (!genConfig.allowOverwrite) {
      throw new Error('系统配置不允许生成文件覆盖到本地');
    }

    const genTable = await GenTable.findOne({
      where: { tableName },
      include: [{
        model: GenTableColumn,
        as: 'columns'
      }]
    });

    if (!genTable) {
      throw new Error(`表 ${tableName} 不存在`);
    }

    // 获取自定义路径
    let genPath = genTable.genPath;
    if (!genPath || genPath === '/') {
      genPath = process.cwd();
    }

    // 预览代码
    const codeMap = await this.previewCode(genTable.tableId);

    // 生成代码到自定义路径
    for (const [fileName, content] of codeMap.entries()) {
      try {
        const filePath = path.join(genPath, fileName);
        const fileDir = path.dirname(filePath);

        // 创建目录
        if (!await existsAsync(fileDir)) {
          await mkdirAsync(fileDir, { recursive: true });
        }

        // 写入文件
        await writeFileAsync(filePath, content);
      } catch (err) {
        console.error(`生成文件 ${fileName} 失败:`, err);
        return false;
      }
    }

    return true;
  }

  /**
   * 批量生成代码（下载方式）
   * @param tableNames 表名数组
   * @returns 代码zip包的buffer
   */
  public async downloadCode(tableNames: string[]): Promise<Buffer> {
    const zip = new JSZip();

    // 逐个生成表代码并添加到zip中
    for (const tableName of tableNames) {
      const genTable = await GenTable.findOne({
        where: { tableName },
        include: [{
          model: GenTableColumn,
          as: 'columns'
        }]
      });

      if (!genTable) {
        continue;
      }

      // 预览代码
      const codeMap = await this.previewCode(genTable.tableId);

      // 添加到zip中
      for (const [fileName, content] of codeMap.entries()) {
        zip.file(fileName, content);
      }
    }

    // 生成zip包
    const buffer = await zip.generateAsync({
      type: 'nodebuffer',
      compression: 'DEFLATE',
      compressionOptions: {
        level: 9
      }
    });

    return buffer;
  }

  /**
   * 验证编辑
   * @param genTable 业务表
   */
  public async validateEdit(genTable: GenTable): Promise<void> {
    if (genTable.tplCategory === 'sub') {
      if (!genTable.subTableName) {
        throw new Error('关联子表的表名不能为空');
      }
      if (!genTable.subTableFkName) {
        throw new Error('子表关联的外键名不能为空');
      }
    }
  }

  /**
   * 获取已生成表名列表
   * @returns 表名列表
   */
  private async getGenTableNames(): Promise<string[]> {
    const tables = await GenTable.findAll({
      attributes: ['tableName']
    });
    
    return tables.map(table => table.tableName);
  }

  /**
   * 查询表的列信息
   * @param tableName 表名
   * @returns 列信息
   */
  private async getDbTableColumns(tableName: string): Promise<any[]> {
    const sql = `SELECT 
                    column_name, 
                    column_comment, 
                    column_type, 
                    column_key, 
                    extra, 
                    is_nullable, 
                    ordinal_position 
                FROM 
                    information_schema.columns 
                WHERE 
                    table_schema = database() 
                    AND table_name = ? 
                ORDER BY 
                    ordinal_position`;
    
    // 执行查询
    const columns = await sequelize.query(sql, {
      replacements: [tableName],
      type: QueryTypes.SELECT
    });
    
    return columns;
  }
}

export default new GenService(); 